package com.weavernorth.salary;

import com.weavernorth.sysap.util.SyncUtil;
import com.weavernorth.util.Logger4jUtil;
import oracle.jdbc.OracleTypes;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.log4j.Logger;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;
import weaver.formmode.setup.ModeRightInfo;
import weaver.general.StaticObj;
import weaver.general.Util;
import weaver.interfaces.datasource.DataSource;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * @Author: bruseewang
 * @Package: com.weavernorth.salary
 * @date: 2020/6/24 14:48
 * @description:
 */
public class AppletUtils {
    public static Logger log = Logger4jUtil.getLoggerByName("/action/GetAppletSaleForV8Timer/");

    public static void getData(String strYearMonth){
        //获取员工信息类
        SyncUtil syncUtil = new SyncUtil();
        String strFormid = syncUtil.getModeIdBytableName("uf_zdxcxxsyj");
        //获取当前年月
//        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
//        Calendar cal = Calendar.getInstance();
//        cal.setTime(new Date()); //设置时间为当前时间
//        cal.add(Calendar.MONTH, -1);
//        String preMonth = format.format(cal.getTime());
//        String year = preMonth.split("-")[0];
//        String month = preMonth.split("-")[1];
//        log.info("当前年:" + year + ",月:" + month);
        //年月
//        String strYearMonth = String.valueOf(year) + String.valueOf(month);
        //数据库操作
        RecordSet rs = new RecordSet();
        //查询当前的店铺信息
        String sql = "select id,dpsapid,dp from uf_dpxx";
        log.info("获取当前店铺信息sql:" + sql);
        rs.execute(sql);
        while (rs.next()) {
            //Sap店铺id
            String strDpSapid = Util.null2String(rs.getString("dpsapid"));
            //当前店铺id
//            String strDpid = Util.null2String(rs.getString("dp"));
            try {
                log.info("获取店铺sap编号:" + strDpSapid);
                //执行存储过程,获取V8数据
                List<AppletSaleEntity> listMess = AppletUtils.getAppletForV8(strYearMonth, strDpSapid);
                log.info("v8返回数据长度:" + (listMess.size()));
                //插入建模表
                if (listMess.size() > 0) {
                    log.info("V8返回的数据："+listMess.toString());
                    AppletUtils.insertAppletSale(strFormid, strYearMonth, listMess,rs);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
    }
    /***
     * 从V8获取小程序信息
     * @param strZq
     * @param strDpSapid
     * @return
     * @throws Exception
     */
    public static List<AppletSaleEntity> getAppletForV8(String strZq, String strDpSapid) throws Exception {
        CallableStatement callStmt = null;
        DataSource ds = null;
        Connection conn = null;
        log.info("进入存储过程:" + strZq + "," + strDpSapid);
        List<AppletSaleEntity> list = new ArrayList<AppletSaleEntity>();
        // 获取sqlserver的数据库连接
        try {
            ds = (DataSource) StaticObj.getServiceByFullname(
                    ("datasource.V8A"), DataSource.class);
            conn = ds.getConnection();
            // 存储过程 P_ERDOS_YJBB_WL_QRY 查询数据
            callStmt = conn.prepareCall("{call fyerp.P_ERDOS_YJBB_WM_QRY(?,?,?,?,?)}");
            callStmt.setString(1, strZq);
            callStmt.setString(2, strDpSapid);
            callStmt.registerOutParameter(3, Types.FLOAT);
            callStmt.registerOutParameter(4, Types.VARCHAR);
            callStmt.registerOutParameter(5, OracleTypes.CURSOR);
            callStmt.execute();
            //获取信息结果集
            ResultSet rsResult = (ResultSet) callStmt.getObject(5);
            //循环结果集
            while (rsResult.next()) {
                AppletSaleEntity appletSaleEntity = new AppletSaleEntity();
                //sap工厂号
                String strSapid = Util.null2String(rsResult.getString(1));
                //sap店铺编码
                String strSapdpbm = Util.null2String(rsResult.getString(2));
                //员工编号
                String strYgbh = Util.null2String(rsResult.getString(3));
                //销售期间
                String strXsqj = Util.null2String(rsResult.getString(4));
                //小程序正价
                String strXcxSale_up = Util.null2String(rsResult.getString(5));
                //小程序折价
                String strXcxSale_dn = Util.null2String(rsResult.getString(6));
                //小程序总流水
                String strXcxSale_zls = Util.null2String(rsResult.getString(7));
                //小程序原始正价
                String strXcxYszj = Util.null2String(rsResult.getString(8));
                //小程序原始折价
                String strXcxYszzj = Util.null2String(rsResult.getString(9));
                appletSaleEntity.setSapgch(strSapid);
                appletSaleEntity.setSapdpbm(strSapdpbm);
                appletSaleEntity.setYgbh(strYgbh);
                appletSaleEntity.setXcxzjxs(strXcxSale_up);
                appletSaleEntity.setXcxzjxss(strXcxSale_dn);
                appletSaleEntity.setXcxxszls(strXcxSale_zls);
                appletSaleEntity.setXcxyszj(strXcxYszj);
                appletSaleEntity.setXcxyszzj(strXcxYszzj);
                list.add(appletSaleEntity);
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.info("执行存储过程发生异常>>>>>>>>" + e.toString());
        } finally {
            if (null != callStmt) {
                callStmt.close();
            }
            if (null != conn) {
                conn.close();
            }
        }
        return list;
    }

    /***
     * 将从V8查取到的数据插入到小程序表中
     * @param strFormid
     * @param strZq
     * @param listMess
     */
    public static void insertAppletSale(String strFormid, String strZq, List<AppletSaleEntity> listMess,RecordSet rs){
        RecordSetTrans rsTrans;
        String strInsert="INSERT INTO uf_zdxcxxsyj (sapgch,sapdpbm,ygbh,xsqj,xcxzjxs,xcxzjxss,xcxxszls,xcxyszj,xcxyszzj,formmodeid,modedatacreater)VALUES(?,?,?,?,?,?,?,?,?,?,?)";
        try {
            for (AppletSaleEntity appletSaleEntity : listMess) {
                String strSapgch=appletSaleEntity.getSapgch();
                String strSapdpbm=appletSaleEntity.getSapdpbm();
                String strYgbh=appletSaleEntity.getYgbh();
                String strXsqj=strZq;//appletSaleEntity.getXsqj();//销售期间
                String strXcxzjxs=appletSaleEntity.getXcxzjxs();//小程序正价销售额
                String strXcxzjxss=appletSaleEntity.getXcxzjxss();//小程序折价销售额
                String strXcxxszls=appletSaleEntity.getXcxxszls();//小程序销售总额
                String strXcxyszj=appletSaleEntity.getXcxyszj();//小程序原始正价
                String strXcxyszzj=appletSaleEntity.getXcxyszzj();//小程序原始折价
                Boolean resultExe=isExists(strZq,strSapgch,strSapdpbm,strYgbh);
                if (!resultExe) {//数据不存在则插入数据
                    rsTrans = new RecordSetTrans();
                    try {
                        rsTrans.setAutoCommit(false);
                        rsTrans.executeQuery(strInsert, strSapgch, strSapdpbm, strYgbh, strXsqj, strXcxzjxs, strXcxzjxss, strXcxxszls,strXcxyszj,strXcxyszzj,strFormid, "1");
                        rsTrans.commit();
                        ModeRightInfo modeRightInfoBanc = new ModeRightInfo();
                        modeRightInfoBanc.setNewRight(true);
                        // 新建的
                        modeRightInfoBanc.editModeDataShare(1, Integer.parseInt(strFormid), Util.getIntValue(rs.getString("id")));
                        log.info("插入到小程序表成功！");
                    } catch (Exception ex) {
                        log.info("插入到小程序表出现异常！");
                        ex.printStackTrace();
                        rsTrans.rollback();
                        break;
                    }
                }else{//存在则更新数据
                    String strUpdate="update uf_zdxcxxsyj set xcxzjxs=?,xcxzjxss=?,xcxxszls=?,xcxyszj=?,xcxyszzj=? where sapgch=? and sapdpbm=? and ygbh=? and xsqj=?";
                    RecordSetTrans rsTrans_u = new RecordSetTrans();
                    try {
                        rsTrans_u.setAutoCommit(false);
                        rsTrans_u.executeQuery(strUpdate,  strXcxzjxs, strXcxzjxss, strXcxxszls,strXcxyszj,strXcxyszzj,strSapgch, strSapdpbm, strYgbh, strXsqj);
                        rsTrans_u.commit();
                        log.info("小程序表更新成功！");
                    }catch (Exception ex){
                        ex.printStackTrace();
                        rsTrans_u.rollback();
                        break;
                    }

                }
            }
        }catch (Exception ex){
            log.info("插入到小程序表出现异常！");
            ex.printStackTrace();
        }
    }

    /***
     * 判断是否已经取到V8数据
     * @param strZq
     * @param strSapgch
     * @param strYgbh
     * @return
     */
    public static Boolean isExists(String strZq,String strSapgch,String sapdpbm,String strYgbh) {
        RecordSet rs = new RecordSet();
        try {
            String strSql = "SELECT * FROM uf_zdxcxxsyj WHERE xsqj='" + strZq + "' AND sapgch='" + strSapgch + "'  AND sapdpbm='" + sapdpbm + "' AND ygbh='" + strYgbh + "'";
            rs.executeQuery(strSql);
            int rows=rs.getCounts();
            if (rows>0){
                return true;
            }else {
                return false;
            }
        }catch (Exception ex){
            ex.printStackTrace();
            log.info("查询数据出现异常："+ex.toString());
            return  false;
        }

    }
}
